package zhyh.kit.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import zhyh.exception.HeHeException;
import zhyh.kit.base.Constants;
import zhyh.kit.base.Kit;
import zhyh.kit.convert.MyConvertKit;
import zhyh.kit.date.DateKit;
import zhyh.kit.excel.annotations.ExcelField;
import zhyh.kit.excel.annotations.ExcelTitle;
import zhyh.kit.string.StringKit;

public class ExcelKit extends Kit
{

	public ExcelKit() {
	}

	public static <T> List<T> getListFromExcel(InputStream inputStream, Class<T> type)
			throws InstantiationException, IllegalAccessException, Exception {

		String filePath = null;
		return getListFromExcel(filePath, 0, 1, type);
	}

	public static <T> List<T> getListFromExcel(String filePath, Class<T> type)
			throws InstantiationException, IllegalAccessException, Exception {
		return getListFromExcel(filePath, 0, 1, type);
	}

	public static <T> List<T> getListFromExcel(String filePath, int startRow, Class<T> type)
			throws InstantiationException, IllegalAccessException, Exception {
		return getListFromExcel(filePath, 0, startRow, type);
	}

	/**
	 * <h4>功能：【读取Excel内容到对象集合中】【2016年10月19日 下午6:31:23】【创建人：ZhangYH】</h4>
	 * <ul>
	 * 	<li></li>
	 * </ul>
	 * @param filePath						Excel文件所在路径
	 * @param sheetAt						工作薄编号
	 * @param startRow						数据开始行
	 * @param type							集合对象类型
	 * @return								type型对象集合
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws Exception
	 */
	@SuppressWarnings({ "resource" })
	public static <T> List<T> getListFromExcel(String filePath, int sheetAt, int startRow, Class<T> type)
			throws InstantiationException, IllegalAccessException, Exception {
		FileInputStream fis = new FileInputStream(filePath);
		Workbook workbook = null;
		if (filePath.toLowerCase().endsWith(Constants.XLSX_TYPE)) {
			workbook = new XSSFWorkbook(fis);
		} else if (filePath.toLowerCase().endsWith(Constants.XLS_TYPE)) {
			workbook = new HSSFWorkbook(fis);
		} else {
			throw new HeHeException(Constants.UNSUPPORT_FILE_TYPE_MSG);
		}

		Sheet sheet = workbook.getSheetAt(sheetAt);
		if (null == sheet) {
			throw new HeHeException(Constants.NULL_SHEET_MGS);
		}
		List<T> result = new ArrayList<T>();
		Iterator<Row> rows = sheet.rowIterator();
		while (rows.hasNext()) {
			Row row = rows.next();
			//若当前行小于指定开始行，跳过本行
			if (row.getRowNum() < startRow)
				continue;
			//空行过滤
			if (isBlankRow(row))
				continue;
			T t = type.newInstance();
			Field[] fs = t.getClass().getDeclaredFields();
			for (Field f : fs) {
				ExcelField ef = f.getAnnotation(ExcelField.class);
				if (null != ef) {
					if (ef.exportOnly())
						continue;
					int index = ef.importCellAt() > -1 ? ef.importCellAt() : ef.cellAt();
					Class<?> fType = f.getType();
					if (null == row.getCell(index) || StringKit.isBlank(row.getCell(index).toString())) {
						//空值跳过
						if (ef.isNotNull()) {
							Method method = type.getSuperclass().getDeclaredMethod(Constants.PUT_ERROR_MSG_METHOD_NAME,
									String.class, String.class);
							method.invoke(t, Constants.NULL_MSG_KEY, getMsg(row, index));
						}
						continue;
					}

					if (ef.valueOfInteger().length > 0) {
						//检测值是否合法
						boolean checkValueOfInteger = checkValueOfInteger(ef, row.getCell(index).getNumericCellValue(),
								type, t, row, index);
						if (!checkValueOfInteger)
							continue;
					}
					if (ef.valueOfDouble().length > 0) {
						//检测值是否合法
						boolean checkValueOfDouble = checkValueOfDouble(ef, row.getCell(index).getNumericCellValue(),
								type, t, row, index);
						if (!checkValueOfDouble)
							continue;
					}
					if (ef.valueOfString().length > 0) {
						//检测值是否合法
						boolean checkValueOfString = checkValueOfString(ef, row.getCell(index).getStringCellValue(),
								type, t, row, index);
						if (!checkValueOfString)
							continue;
					}
					if (ef.sectionOfDouble().length == 2) {
						//校验值是否合法（区间）
						boolean checkSectionOfDouble = checkSectionOfDouble(ef,
								row.getCell(index).getNumericCellValue(), type, t, row, index);
						if (!checkSectionOfDouble)
							continue;
					}
					if (ef.sectionOfInteger().length == 2) {
						//校验值是否合法（区间）
						boolean checkSectionOfInteger = checkSectionOfInteger(ef,
								row.getCell(index).getNumericCellValue(), type, t, row, index);
						if (!checkSectionOfInteger)
							continue;
					}
					if (ef.format()) {//反编码,例如将表格中的男、女转为1,0
						Method m = type.getDeclaredMethod(f.getName() + Constants.DECODE_NAME, String.class);
						Method mf = type.getMethod(Constants.SET_NAME + StringKit.firstCharToUpperCase(f.getName()),
								fType);
						Object r = m.invoke(t, row.getCell(index).getStringCellValue());
						mf.invoke(t, r);
					} else {
						Method m = type.getMethod(Constants.SET_NAME + StringKit.firstCharToUpperCase(f.getName()),
								fType);
						String fTypeName = fType.getName().substring(fType.getName().lastIndexOf(".") + 1);
						if (null == row.getCell(index) || StringKit.isBlank(row.getCell(index).toString()))
							continue;
						if ("Date".equals(fTypeName)) {
							m.invoke(t, DateKit.strToDate(row.getCell(index).getStringCellValue(), ef.dateFormat()));
						} else if (null != fType.getSuperclass()
								&& "java.lang.Number".equals(fType.getSuperclass().getName())) {
							Object cellValue = MyConvertKit.convertFromDouble(row.getCell(index).getNumericCellValue(),
									f.getType().getName());
							m.invoke(t, cellValue);
						} else {
							row.getCell(index).setCellType(CellType.STRING);
							String cellValue = row.getCell(index).getStringCellValue();
							m.invoke(t, cellValue);
						}
					}
				}
			}
			result.add(t);
		}
		fis.close();
		return result;
	}

	private static boolean checkSectionOfInteger(ExcelField ef, double cellValue, Class<? extends Object> type,
			Object t, Row row, int index) throws SecurityException, NoSuchMethodException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		int[] ck = ef.sectionOfInteger();
		if ((int) cellValue >= ck[0] && (int) cellValue <= ck[1])
			return true;
		Method method = type.getSuperclass().getDeclaredMethod(Constants.PUT_ERROR_MSG_METHOD_NAME, String.class,
				String.class);
		method.invoke(t, Constants.INT_SECTION, getMsg(row, index, ck[0], ck[1]));
		return false;
	}

	private static boolean checkSectionOfDouble(ExcelField ef, double cellValue, Class<? extends Object> type, Object t,
			Row row, int index) throws SecurityException, NoSuchMethodException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		double[] ck = ef.sectionOfDouble();
		if (cellValue >= ck[0] && cellValue <= ck[1])
			return true;
		Method method = type.getSuperclass().getDeclaredMethod(Constants.PUT_ERROR_MSG_METHOD_NAME, String.class,
				String.class);
		method.invoke(t, Constants.DOUBLE_SECTION, getMsg(row, index, ck[0], ck[1]));
		return false;
	}

	private static String getMsg(Row row, int cellAt, Number d, Number e) {
		String format = String.format(getProp(Constants.SECTION_KEY), (row.getRowNum() + 1), cellAt + 1, d, e);
		return format;
	}

	private static boolean checkValueOfString(ExcelField ef, String cellValue, Class<? extends Object> type, Object t,
			Row row, int index) throws SecurityException, NoSuchMethodException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		String[] ck = ef.valueOfString();
		boolean result = false;
		for (int i = 0; i < ck.length; i++) {
			if (cellValue.equals(ck[i]))
				result = true;
		}
		if (!result) {
			Method method = type.getSuperclass().getDeclaredMethod(Constants.PUT_ERROR_MSG_METHOD_NAME, String.class,
					String.class);
			method.invoke(t, Constants.STRING_VALUE, getMsg(row, index, ck));
		}
		return result;
	}

	private static Object getMsg(Row row, int cellAt, String[] ck) {
		return String.format(getProp(Constants.ARR_VALUE_KEY), row.getRowNum() + 1, cellAt + 1, Arrays.toString(ck));
	}

	private static boolean checkValueOfDouble(ExcelField ef, Object cellValue, Class<? extends Object> type, Object t,
			Row row, int index) throws SecurityException, NoSuchMethodException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		double[] ck = ef.valueOfDouble();
		boolean ckResult = false;
		for (int i = 0; i < ck.length; i++) {
			if ((Double) cellValue == ck[i])
				ckResult = true;
		}
		if (!ckResult) {
			Method method = type.getSuperclass().getDeclaredMethod(Constants.PUT_ERROR_MSG_METHOD_NAME, String.class,
					String.class);
			method.invoke(t, Constants.DOUBLE_VALUE, getMsg(row, index, ck));
		}
		return ckResult;
	}

	private static boolean checkValueOfInteger(ExcelField ef, Object cellValue, Class<? extends Object> type, Object t,
			Row row, int index) throws SecurityException, NoSuchMethodException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		int[] ckInt = ef.valueOfInteger();
		boolean ckIntResult = false;
		for (int i = 0; i < ckInt.length; i++) {
			if ((Integer) cellValue == ckInt[i])
				ckIntResult = true;
		}
		if (!ckIntResult) {
			Method method = type.getSuperclass().getDeclaredMethod(Constants.PUT_ERROR_MSG_METHOD_NAME, String.class,
					String.class);
			method.invoke(t, Constants.INT_VALUE, getMsg(row, index, ckInt));
		}
		return ckIntResult;
	}

	private static String getMsg(Row row, int cellAt, double[] ck) {
		return String.format(getProp(Constants.ARR_VALUE_KEY), row.getRowNum() + 1, cellAt + 1, Arrays.toString(ck));
	}

	private static String getMsg(Row row, int cellAt, int[] ckInt) {
		return String.format(getProp(Constants.ARR_VALUE_KEY), row.getRowNum() + 1, cellAt + 1, Arrays.toString(ckInt));
	}

	private static String getMsg(Row row, int cellAt) {
		return String.format(getProp(Constants.NULL_MSG_KEY), row.getRowNum() + 1, cellAt + 1);
	}

	private static boolean isBlankRow(Row row) {
		List<Object> filter = new ArrayList<Object>();
		for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
			Object value = null;
			if (null == row.getCell(i))
				continue;
			try {
				value = row.getCell(i).getStringCellValue();
			} catch (Exception e) {
				try {
					value = row.getCell(i).getBooleanCellValue();
				} catch (Exception e1) {
					try {
						value = row.getCell(i).getDateCellValue();
					} catch (Exception e2) {
						try {
							value = row.getCell(i).getNumericCellValue();
						} catch (Exception e3) {
							try {
								value = row.getCell(i).getRichStringCellValue();
							} catch (Exception e4) {
								value = row.getCell(i).getErrorCellValue();
							}
						}
					}
				}
			}
			if (null != value && StringKit.isNotBlank(value.toString()))
				filter.add(value);
		}
		return filter.size() > 0 ? false : true;
	}

	public static <T> void writeListToFile(String fileName, List<T> data, int startRow) throws Exception {
		writeListToFile(fileName, data, 0, startRow);
	}

	public static <T> void writeListToFile(String fileName, List<T> data) throws Exception {
		writeListToFile(fileName, data, 0, 1);
	}

	/**
	 * <h4>功能：【将集合内容写入到Excel文件中】【2016年10月18日 下午6:42:32】【创建人：ZhangYH】</h4>
	 * @param fileName  	Excel文件位置
	 * @param t				写入数据集合
	 * @param sheetAt		工作簿位置
	 * @param startRow		写入Excel文件时从第几行开始(从0开始)
	 * @throws Exception
	 */
	@SuppressWarnings("resource")
	public static <T> void writeListToFile(String fileName, List<T> data, int sheetAt, int startRow) throws Exception {
		File file = new File(fileName);
		if (!file.exists()) {
			file.createNewFile();
			FileOutputStream stream = new FileOutputStream(file);
			Workbook workbook = null;
			if (fileName.toLowerCase().endsWith(Constants.XLSX_TYPE)) {
				workbook = new XSSFWorkbook();
			} else if (fileName.toLowerCase().endsWith(Constants.XLS_TYPE)) {
				workbook = new HSSFWorkbook();
			}
			workbook.createSheet();
			workbook.write(stream);
			stream.close();
		}
		if (null == data || data.size() == 0) {
			throw new HeHeException(Constants.NULL_LIST_MSG);
		}
		Workbook workbook = null;
		if (fileName.endsWith(Constants.XLSX_TYPE)) {
			workbook = new XSSFWorkbook(new FileInputStream(fileName));
		} else if (fileName.endsWith(Constants.XLS_TYPE)) {
			workbook = new HSSFWorkbook(new FileInputStream(fileName));
		} else {
			throw new Exception(Constants.UNSUPPORT_FILE_TYPE_MSG);
		}
		Sheet sheet = null;
		try {
			sheet = workbook.getSheetAt(sheetAt);
		} catch (Exception e) {
			sheet = workbook.createSheet();
		}
		Iterator<T> iterator = data.iterator();
		//表格标题
		T tt = data.get(0);
		ExcelTitle excelTitle = tt.getClass().getAnnotation(ExcelTitle.class);
		if (excelTitle != null) {
			String[] title = excelTitle.exportTitle().length > 0 ? excelTitle.exportTitle() : excelTitle.title();

			int cellStartAt = excelTitle.cellStartAt();
			if (title.length > 0) {
				Row row = sheet.createRow(excelTitle.rowStartAt());
				for (int i = 0; i < title.length; i++) {
					Cell cell = row.createCell(cellStartAt++);
					cell.setCellValue(title[i]);
				}
			}
		}
		//表格内容
		while (iterator.hasNext()) {
			T rt = iterator.next();
			Row row = sheet.createRow(startRow++);
			Field[] fields = rt.getClass().getDeclaredFields();
			for (Field f : fields) {
				ExcelField an = f.getAnnotation(ExcelField.class);
				if (null != an) {
					if (an.importOnly()) //字段仅导入
						continue;
					int index = an.exportCellAt() > -1 ? an.exportCellAt() : an.cellAt();
					Cell cell = row.createCell(index);
					if (an.format()) {
						Method m = rt.getClass().getDeclaredMethod(f.getName() + Constants.ENCODE_NAME);
						cell.setCellValue(m.invoke(rt).toString());
					} else {
						Method[] ms = rt.getClass().getDeclaredMethods();
						for (Method m : ms) {
							if (m.getName().toUpperCase()
									.startsWith((Constants.GET_NAME + f.getName()).toUpperCase())) {
								Object cv = m.invoke(rt);
								if (null == cv)
									break;
								else if (cv instanceof String) {
									cell.setCellValue(cv.toString());
								} else if (cv instanceof Date) {
									cell.setCellValue(new SimpleDateFormat(an.dateFormat()).format((Date) cv));
								} else if (cv instanceof Number) {
									cell.setCellValue(((Number) cv).doubleValue());
								} else {
									cell.setCellValue(cv.toString());
								}
								break;
							}
						}
					}

				}
			}
		}
		FileOutputStream fos = new FileOutputStream(fileName);
		workbook.write(fos);
		fos.close();
	}

	
	/**
	 * 将集合内容写入到Excel文件中
	 * @param fileName  Excel文件位置
	 * @param data		{sheetName:写入数据集合}
	 * @param sheetAt	开始工作薄位置
	 * @param startRow	开始行
	 * @throws Exception
	 */
	@SuppressWarnings("resource")
	public static <T> void writeListToFile(String fileName, Map<String, List<T>> data, int sheetAt, int startRow)
			throws Exception {

		int initStartRow = startRow;

		File file = new File(fileName);
		if (!file.exists()) {
			file.createNewFile();
			FileOutputStream stream = new FileOutputStream(file);
			Workbook workbook = null;
			if (fileName.toLowerCase().endsWith(Constants.XLSX_TYPE)) {
				workbook = new XSSFWorkbook();
			} else if (fileName.toLowerCase().endsWith(Constants.XLS_TYPE)) {
				workbook = new HSSFWorkbook();
			}
			workbook.createSheet();
			workbook.write(stream);
			stream.close();
		}
		if (null == data || data.size() == 0) {
			throw new HeHeException(Constants.NULL_LIST_MSG);
		}
		Workbook workbook = null;
		if (fileName.endsWith(Constants.XLSX_TYPE)) {
			workbook = new XSSFWorkbook(new FileInputStream(fileName));
		} else if (fileName.endsWith(Constants.XLS_TYPE)) {
			workbook = new HSSFWorkbook(new FileInputStream(fileName));
		} else {
			throw new Exception(Constants.UNSUPPORT_FILE_TYPE_MSG);
		}
		Sheet sheet = null;

		Iterator<String> sheetNameIterator = data.keySet().iterator();
		while (sheetNameIterator.hasNext()) {
			startRow = initStartRow;
			String sheetName = sheetNameIterator.next();
			try {
				sheet = workbook.getSheetAt(sheetAt);
				workbook.setSheetName(sheetAt++, sheetName);
			} catch (Exception e) {
				sheet = workbook.createSheet(sheetName);
				e.printStackTrace();
			}

			Iterator<T> iterator = data.get(sheetName).iterator();
			//表格标题
			T tt = data.get(sheetName).get(0);
			ExcelTitle excelTitle = tt.getClass().getAnnotation(ExcelTitle.class);
			if (excelTitle != null) {
				String[] title = excelTitle.exportTitle().length > 0 ? excelTitle.exportTitle() : excelTitle.title();

				int cellStartAt = excelTitle.cellStartAt();
				if (title.length > 0) {
					Row row = sheet.createRow(excelTitle.rowStartAt());
					for (int i = 0; i < title.length; i++) {
						Cell cell = row.createCell(cellStartAt++);
						cell.setCellValue(title[i]);
					}
				}
			}
			//表格内容
			while (iterator.hasNext()) {
				T rt = iterator.next();
				Row row = sheet.createRow(startRow++);
				Field[] fields = rt.getClass().getDeclaredFields();
				for (Field f : fields) {
					ExcelField an = f.getAnnotation(ExcelField.class);
					if (null != an) {
						if (an.importOnly()) //字段仅导入
							continue;
						int index = an.exportCellAt() > -1 ? an.exportCellAt() : an.cellAt();
						Cell cell = row.createCell(index);
						if (an.format()) {
							Method m = rt.getClass().getDeclaredMethod(f.getName() + Constants.ENCODE_NAME);
							cell.setCellValue(m.invoke(rt).toString());
						} else {
							Method[] ms = rt.getClass().getDeclaredMethods();
							for (Method m : ms) {
								if (m.getName().toUpperCase()
										.startsWith((Constants.GET_NAME + f.getName()).toUpperCase())) {
									Object cv = m.invoke(rt);
									if (null == cv)
										break;
									else if (cv instanceof String) {
										cell.setCellValue(cv.toString());
									} else if (cv instanceof Date) {
										cell.setCellValue(new SimpleDateFormat(an.dateFormat()).format((Date) cv));
									} else if (cv instanceof Number) {
										cell.setCellValue(((Number) cv).doubleValue());
									} else {
										cell.setCellValue(cv.toString());
									}
									break;
								}
							}
						}

					}
				}
			}
			FileOutputStream fos = new FileOutputStream(fileName);
			workbook.write(fos);
			fos.close();

		}

	}
}